package com.lh.common.utils;

import com.lh.common.annotation.ExcelExportRule;
import com.lh.common.annotation.ExcelFile;
import com.lh.common.exception.ExcelException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;

/**
 * @Description: EXL报表的生成与读入
 * @author TangCai
 * @date 2016年11月3日 上午11:00:27
 * @version V1.0
 */
public class ExcelUtilJXL {
    /**
     * 通过字节流向客户写入exel文件
     * @throws UnsupportedEncodingException
     *
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public static void exportExcelToBrowserForJSP(Class<?> clazz, List<?> list, String fileName,HttpServletResponse response) throws Exception {
        ExcelFile ef = (ExcelFile) clazz.getAnnotation(ExcelFile.class);
        response.reset();
        response.setContentType("application/download");
        if(fileName == null) {
            fileName = ef.fileName();
        }
        fileName = java.net.URLEncoder.encode(fileName, ef.encoding());
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        ServletOutputStream ouputStream = response.getOutputStream();
        writerExel(clazz, list, ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }

    /**
     * 通过字节流向客户写入exel文件(同一个sheet可写入两种数据)
     * @throws UnsupportedEncodingException
     *
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public static void exportExcelToBrowserForJSP2(Class<?> clazz1, List<?> list1, Class<?> clazz2, List<?> list2,String fileName,HttpServletResponse response) throws Exception {
        ExcelFile ef = (ExcelFile) clazz1.getAnnotation(ExcelFile.class);
        response.reset();
        response.setContentType("application/download");
        if(fileName == null) fileName = ef.fileName();
        fileName = java.net.URLEncoder.encode(fileName, ef.encoding());
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        ServletOutputStream ouputStream = response.getOutputStream();
        writerExel1(clazz1, list1, clazz2, list2, ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }

    /**
     * 写入生成exel文件 固定输出方式 @ExcelFile中设置路径与文件名
     * @throws Exception
     *
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public static void writerExel(Class<?> clazz, List<?> list) throws Exception {
        ExcelFile ef = (ExcelFile) clazz.getAnnotation(ExcelFile.class);
        String path = ef.path();
        if (path.lastIndexOf("/") != (path.length() - 1))
            path = path + "/";
        writerExel(clazz, list, path + ef.fileName());
    }

    /**
     * 写入生成exel文件 不定输出方式
     * obj可为String(输出路径)或者OutputStream
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public static void writerExel(Class<?> clazz, List<?> list, Object obj)
            throws Exception {
        if (list == null) {
            list = new ArrayList<Object>();
        }
        Field[] fieldes = clazz.getDeclaredFields();
        WritableWorkbook book = null; // 第一步
        if (obj instanceof String) {
            book = Workbook.createWorkbook(new File((String) obj));
        }
        if (obj instanceof OutputStream) {
            book = Workbook.createWorkbook((OutputStream) obj);
        }
        ExcelFile ef = (ExcelFile) clazz.getAnnotation(ExcelFile.class);
        if (ef == null) {
            throw new ExcelException("@ExcelFile注释不能为空");
        }
        if (book == null) {
            throw new ExcelException("OutputStream obj error!!");
        }
        WritableSheet sheet = book.createSheet(ef.titleName(), 0); // 创建Sheet
        WritableFont titlewf = null;
        WritableFont contentwf = null;
        List<Roow> roowes = new ArrayList<Roow>();
        int xValue = 0, yValue = 0, rowSize = 0;

        String[] titleInfo = ef.titleInfo();
        if(titleInfo != null && titleInfo.length>0){//添加标题信息
            for(String titInfo:titleInfo){
                titlewf = new WritableFont(WritableFont.ARIAL,  10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
                sheet.mergeCells(xValue,yValue,fieldes.length-1,yValue);
                sheet.setColumnView(xValue, 20);
                sheet.addCell(new Label(xValue, yValue, titInfo, new WritableCellFormat(titlewf)));
                yValue++;
            }
        }
        Roow roow0 = null;
        rowSize = ef.rowView();
        String methodStr = null, name;
        String[] names;
        Class<?> call;
        for (Field field : fieldes) {
            ExcelExportRule eer = (ExcelExportRule) field.getAnnotation(ExcelExportRule.class);
            if (eer != null) {
                names = eer.label();
                name = field.getName();
                if (eer.objectLabel().length == 0) {
                    if (names.length != 1)
                        throw new ExcelException("names设置的数据长度不正确");
                    methodStr = name;
                    call = clazz;
                } else {
                    if (names.length != eer.objectLabel().length)
                        throw new ExcelException("label()与objectLabel()不等");
                    call = field.getType();
                }
                for (int i = 0; i < names.length; i++) {
                    titlewf = new WritableFont(WritableFont.ARIAL, i < eer.size().length ? eer.size()[i] + 2 : 10,
                            WritableFont.BOLD, i < eer.isUnderline().length ? eer.isUnderline()[i] : false,
                            UnderlineStyle.NO_UNDERLINE, i < eer.colour().length ? eer.colour()[i].get()
                            : jxl.format.Colour.BLACK); // 定义格式
                    // 字体
                    // 下划线
                    // 斜体
                    // 粗体
                    // 颜色
                    contentwf = new WritableFont(WritableFont.ARIAL, i < eer.size().length ? eer.size()[i] : 8,
                            WritableFont.NO_BOLD, i < eer.isUnderline().length ? eer.isUnderline()[i] : false,
                            UnderlineStyle.NO_UNDERLINE, i < eer.colour().length ? eer.colour()[i].get()
                            : jxl.format.Colour.BLACK); // 定义格式
                    // 字体
                    // 下划线
                    // 斜体
                    // 粗体
                    // 颜色
                    sheet.setColumnView(xValue, i < eer.columnView().length ? eer.columnView()[i] : 20);
                    sheet.addCell(new Label(xValue, yValue, names[i], new WritableCellFormat(titlewf)));
                    xValue++;
                    if (methodStr == null)
                        methodStr = eer.objectLabel()[i];
                    else
                        name = null;
                    roow0 = new Roow(name, new PropertyDescriptor(methodStr, call).getReadMethod(),
                            new WritableCellFormat(contentwf),
                            i < eer.isNextMerge().length ? eer.isNextMerge()[i] : false);
                    roowes.add(roow0);
                    methodStr = null;
                }
            }
        }
        yValue++;
        xValue = 0;
        String lastLabel = null;
        Method method;
        Object obj1 = null, obj0 = null;
        for (Object object : list) {
            sheet.setRowView(yValue, rowSize);
            for (Roow roow : roowes) {
                if (roow.obj == null) {
                    if(object.getClass()!=clazz)
                        method = object.getClass().getMethod(roow.method.getName());
                    else method = roow.method;

                    obj0 = method.invoke(object);
                    if (obj0 == null) obj0 = "";

                    roow.merge(sheet, obj0.toString(), xValue, yValue);
                    sheet.addCell(new Label(xValue, yValue, obj0 instanceof Date ? DateTimeUtil.getDateTime((Date) obj0,DateTimeUtil.DEFAULT_DATETIME_FORMAT)
                            : obj0.toString(), roow.wcf));
                    xValue++;
                } else {
                    if (lastLabel == null || !lastLabel.equals(roow.obj)) {
                        lastLabel = roow.obj;
                        method = new PropertyDescriptor(roow.obj, object.getClass()).getReadMethod();
                        obj0 = method.invoke(object);
                    }
                    method = roow.method;
                    obj1 = method.invoke(obj0);
                    if (obj1 == null)
                        obj1 = "";

                    roow.merge(sheet, obj1.toString(), xValue, yValue);
                    sheet.addCell(new Label(xValue, yValue, obj1 instanceof Date ? DateTimeUtil.getDateTime((Date) obj1,DateTimeUtil.DEFAULT_DATETIME_FORMAT)
                            : obj1.toString(), roow.wcf));
                    xValue++;
                }
            }
            lastLabel = null;
            yValue++;
            xValue = 0;
        }
        roowes.clear();
        book.write();
        book.close();
    }

    /**
     * 写入生成exel文件 不定输出方式
     * obj可为String(输出路径)或者OutputStream
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public static void writerExel1(Class<?> clazz1, List<?> list1,Class<?> clazz2, List<?> list2, Object obj)
            throws Exception {
        if (list1 == null) list1 = new ArrayList<Object>();
        if (list2 == null) list2 = new ArrayList<Object>();

        WritableWorkbook book = null; // 第一步
        if (obj instanceof String)
            book = Workbook.createWorkbook(new File((String) obj));
        if (obj instanceof OutputStream)
            book = Workbook.createWorkbook((OutputStream) obj);
        ExcelCoordinate excelCoordinate = new ExcelCoordinate(0,0,0);
        ExcelFile ef = (ExcelFile) clazz1.getAnnotation(ExcelFile.class);
        if (ef == null)
            throw new ExcelException("@ExcelFile注释不能为空");

        WritableSheet sheet = book.createSheet(ef.titleName(), 0); // 创建Sheet
        //写第一类数据
        writeData(sheet, excelCoordinate, clazz1, list1);
        //写第2类数据
        writeData(sheet, excelCoordinate, clazz2, list2);
        book.write();
        book.close();
    }

    private static void writeData(WritableSheet sheet, ExcelCoordinate excelCoordinate,  Class<?> clazz,List<?> list ) throws Exception {
        int  xValue = excelCoordinate.getXValue();
        int  yValue = excelCoordinate.getYValue();
        int  rowSize = excelCoordinate.getRowSize();
        ExcelFile ef = (ExcelFile) clazz.getAnnotation(ExcelFile.class);
        if (ef == null)
            throw new ExcelException("@ExcelFile注释不能为空");
        WritableFont titlewf = null;
        WritableFont contentwf = null;
        Field[] fieldes = clazz.getDeclaredFields();
        List<Roow> roowes = new ArrayList<Roow>();
        String[] titleInfo = ef.titleInfo();
        if(titleInfo != null && titleInfo.length>0){//添加标题信息
            for(String titInfo:titleInfo){
                titlewf = new WritableFont(WritableFont.ARIAL,  10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
                sheet.mergeCells(xValue,yValue,fieldes.length-1,yValue);
                sheet.setColumnView(xValue, 20);
                sheet.addCell(new Label(xValue, yValue, titInfo, new WritableCellFormat(titlewf)));
                yValue++;
            }
        }

        Roow roow0 = null;
        rowSize = ef.rowView();
        String methodStr = null, name;
        String[] names;
        Class<?> call;
        for (Field field : fieldes) {
            ExcelExportRule eer = (ExcelExportRule) field.getAnnotation(ExcelExportRule.class);
            if (eer != null) {
                names = eer.label();
                name = field.getName();
                if (eer.objectLabel().length == 0) {
                    if (names.length != 1)
                        throw new ExcelException("names设置的数据长度不正确");
                    methodStr = name;
                    call = clazz;
                } else {
                    if (names.length != eer.objectLabel().length)
                        throw new ExcelException("label()与objectLabel()不等");
                    call = field.getType();
                }
                for (int i = 0; i < names.length; i++) {
                    titlewf = new WritableFont(WritableFont.ARIAL, i < eer.size().length ? eer.size()[i] + 2 : 10,
                            WritableFont.BOLD, i < eer.isUnderline().length ? eer.isUnderline()[i] : false,
                            UnderlineStyle.NO_UNDERLINE, i < eer.colour().length ? eer.colour()[i].get()
                            : jxl.format.Colour.BLACK); // 定义格式
                    // 字体
                    // 下划线
                    // 斜体
                    // 粗体
                    // 颜色
                    contentwf = new WritableFont(WritableFont.ARIAL, i < eer.size().length ? eer.size()[i] : 8,
                            WritableFont.NO_BOLD, i < eer.isUnderline().length ? eer.isUnderline()[i] : false,
                            UnderlineStyle.NO_UNDERLINE, i < eer.colour().length ? eer.colour()[i].get()
                            : jxl.format.Colour.BLACK); // 定义格式
                    // 字体
                    // 下划线
                    // 斜体
                    // 粗体
                    // 颜色
                    sheet.setColumnView(xValue, i < eer.columnView().length ? eer.columnView()[i] : 20);
                    sheet.addCell(new Label(xValue, yValue, names[i], new WritableCellFormat(titlewf)));
                    xValue++;
                    if (methodStr == null)
                        methodStr = eer.objectLabel()[i];
                    else
                        name = null;
                    roow0 = new Roow(name, new PropertyDescriptor(methodStr, call).getReadMethod(),
                            new WritableCellFormat(contentwf),
                            i < eer.isNextMerge().length ? eer.isNextMerge()[i] : false);
                    roowes.add(roow0);
                    methodStr = null;
                }
            }
        }
        yValue++;
        xValue = 0;
        String lastLabel = null;
        Method method;
        Object obj1 = null, obj0 = null;
        for (Object object : list) {
            sheet.setRowView(yValue, rowSize);
            for (Roow roow : roowes) {
                if (roow.obj == null) {
                    if(object.getClass()!=clazz)
                        method = object.getClass().getMethod(roow.method.getName());
                    else method = roow.method;

                    obj0 = method.invoke(object);
                    if (obj0 == null) obj0 = "";

                    roow.merge(sheet, obj0.toString(), xValue, yValue);
                    sheet.addCell(new Label(xValue, yValue, obj0 instanceof Date ? DateTimeUtil.getDateTime((Date) obj0,DateTimeUtil.DEFAULT_DATETIME_FORMAT)
                            : obj0.toString(), roow.wcf));
                    xValue++;
                } else {
                    if (lastLabel == null || !lastLabel.equals(roow.obj)) {
                        lastLabel = roow.obj;
                        method = new PropertyDescriptor(roow.obj, object.getClass()).getReadMethod();
                        obj0 = method.invoke(object);
                    }
                    method = roow.method;
                    obj1 = method.invoke(obj0);
                    if (obj1 == null)
                        obj1 = "";

                    roow.merge(sheet, obj1.toString(), xValue, yValue);
                    sheet.addCell(new Label(xValue, yValue, obj1 instanceof Date ? DateTimeUtil.getDateTime((Date) obj1,DateTimeUtil.DEFAULT_DATETIME_FORMAT)
                            : obj1.toString(), roow.wcf));
                    xValue++;
                }
            }
            lastLabel = null;
            yValue++;
            xValue = 0;
        }
        roowes.clear();
        excelCoordinate.setXValue(xValue);
        excelCoordinate.setYValue(yValue);
        excelCoordinate.setRowSize(rowSize);
    }

    /**
     * 读入exel文件 固定输入路径与文件 @ExcelFile中设置路径与文件名
     *
     * @throws BiffException
     * @throws IOException
     * @throws ExcelException
     */
    public static List<?> readExel(Class<?> clazz) throws Exception {
        ExcelFile ef = (ExcelFile) clazz.getAnnotation(ExcelFile.class);
        String path = ef.path();
        if (path.lastIndexOf("/") != (path.length() - 1))
            path = path + "/";
        return readExel(clazz, path + ef.fileName());
    }

    /**
     * 读入exel文件 不定输入路径与文件
     *
     * @throws BiffException
     * @throws IOException
     * @throws ExcelException
     */
    public static List<?> readExel(Class<?> clazz, String fileName) throws Exception {
        return readExel(clazz,new File(fileName));
    }

    /**
     * 读入exel文件 不定输入路径与文件
     *
     * @throws BiffException
     * @throws IOException
     * @throws ExcelException
     */
    public static List<?> readExel(Class<?> clazz, File file) throws Exception {
        List<Object> objList = new ArrayList<Object>();
        Workbook book = Workbook.getWorkbook(file);
        Field[] fieldes = clazz.getDeclaredFields();
        List<Roow> roowes = new ArrayList<Roow>();
        List<String> nameList = new ArrayList<String>();
        String[] names;
        Roow roow = null;
        String name, methodStr = null;
        Class<?> clas = null, call;
        for (Field field : fieldes) {
            ExcelExportRule eer = (ExcelExportRule) field.getAnnotation(ExcelExportRule.class);
            if (eer != null) {
                names = eer.label();
                name = field.getName();
                if (eer.objectLabel().length == 0) {
                    if (names.length != 1)
                        throw new ExcelException("names设置的数据长度不正确");
                    call = clazz;
                    methodStr = name;
                } else {
                    if (names.length != eer.objectLabel().length)
                        throw new ExcelException("label()与objectLabel()不等");
                    call = field.getType();
                }
                for (int i = 0; i < names.length; i++) {
                    if (methodStr == null) {
                        methodStr = eer.objectLabel()[i];
                        clas = field.getType().getDeclaredField(eer.objectLabel()[i]).getType();
                    } else {
                        name = null;
                        clas = field.getType();
                    }
                    roow = new Roow(name, new PropertyDescriptor(methodStr, call).getWriteMethod(), clas);
                    roowes.add(roow);
                    nameList.add(names[i].trim());
                    methodStr = null;
                }
            }
        }
        // 获得第一个工作表对象
        Sheet sheet = book.getSheet(0);
        Cell cell = null;
        // 得到第一列第一行的单元格
        int rows = sheet.getRows();
        int column = sheet.getColumns();
        int ki = 0;
        Map<Integer, Roow> columnMap = new HashMap<Integer, Roow>();
        for (int j = 0; j < column; j++) {// 取出对应的label
            cell = sheet.getCell(j, 0);
            String result = cell.getContents();
            ki = nameList.indexOf(result.trim());
            if (ki > -1)
                columnMap.put(j, roowes.get(ki));
        }
        roowes.clear();
        nameList.clear();
        Object object0 = null, obj0 = null;
        String lastLabel = null;
        Method method = null;
        for (int i = 1; i < rows; i++) {
            object0 = clazz.newInstance();// 利用反射new新对象
            for (int k : columnMap.keySet()) {
                roow = columnMap.get(k);
                cell = sheet.getCell(k, i);
                String result = cell.getContents().trim();
                if (roow.obj == null) {
                    method = roow.method;
                    method.invoke(object0, roow.get(result));
                } else {
                    if (lastLabel == null) {
                        lastLabel = roow.obj;
                        name = lastLabel;
                        clas = clazz.getDeclaredField(name).getType();
                        obj0 = clas.newInstance();
                    } else if (!lastLabel.equals(roow.obj)) {
                        method = clazz.getMethod(lastLabel, clas);
                        method.invoke(object0, obj0);
                        lastLabel = roow.obj;
                        name = roow.obj;
                        clas = clazz.getDeclaredField(name).getType();
                        obj0 = clas.newInstance();
                    }
                    method = roow.method;
                    method.invoke(obj0, roow.get(result));
                }
            }
            if (obj0 != null) {
                method = new PropertyDescriptor(lastLabel, clazz).getWriteMethod();
                method.invoke(object0, obj0);
            }
            lastLabel = null;
            objList.add(object0);
        }
        columnMap.clear();
        book.close();
        return objList;
    }

    private static class Roow {
        private String obj;
        private Method method;
        private WritableCellFormat wcf;
        private Class<?> clazz;

        private boolean isNextMerge=false;
        private int x,y,sum;
        private String val = null;

        private Roow(String obj, Method method, WritableCellFormat wcf,boolean isNextMerge) {
            this.obj = obj;
            this.method = method;
            this.wcf = wcf;
            this.isNextMerge = isNextMerge;
        }

        private Roow(String obj, Method method, Class<?> clazz) {
            this.obj = obj;
            this.method = method;
            this.clazz = clazz;
        }
        //行级合并
        public void merge(WritableSheet sheet,String val,int x,int y) throws RowsExceededException, WriteException{
            if(this.isNextMerge){
                if(val != null && val.equals(this.val)) this.sum += 1;
                else {
                    if(val != null && this.x!=this.sum)
                        sheet.mergeCells(this.x,this.y,this.x,this.sum);
                    this.val = val;
                    this.x = x;
                    this.y = y;
                    this.sum = this.y;
                }
            }

        }

        public Object get(String val) throws ExcelException {
            Object obj = null;
            String name = clazz.getSimpleName();
            if (name.equals("String"))
                obj = val;
            else if (name.equals("Date"))
                obj = DateTimeUtil.parse(val, DateTimeUtil.DEFAULT_DATETIME_FORMAT);
            else if (name.equals("int") || name.equals("Integer"))
                obj = new Integer(val);
            else if (name.equals("BigDecimal"))
                obj = new BigDecimal(val);
            else if (name.equals("short") || name.equals("Short"))
                obj = new Short(val);
            else if (name.equals("long") || name.equals("Long"))
                obj = new Long(val);
            else if (name.equals("float") || name.equals("Float"))
                obj = new Float(val);
            else if (name.equals("double") || name.equals("Double"))
                obj = new Double(val);
            else
                throw new ExcelException("实例化不了此class类型：" + clazz.getName());
            return obj;
        }
    }
}
